﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using BadmintonApp.Entity;
using BadmintonApp.DAO.Interface;

namespace BadmintonApp.DAO.Implement
{
    public class UsersOfActivityDAO : BaseDAO, IUsersOfActivityDAO
    {
        private int GetMaxID()
        {
            string sql = "Select IsNull(Max(FID), 0) from TUsersOfActivity";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            return (int)db.ExecuteScalar(selectCmd);
        }

        #region IUsersOfActivityDAO Members

        /// <summary>
        /// 插入用户与活动的对应关系
        /// </summary>
        /// <param name="userOfActivityInfo">用户与活动的对应关系信息类</param>
        public void InsertUsersOfActivity(TUsersOfActivityInfo userOfActivityInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("Insert into TUsersOfActivity (FID, FActivity, FUserID, FMoney, FMemo, FJoined) Values (");
            sbSQL.Append(" @FID, @FActivityID, @FUserID, @FMoney, @FMemo, @FJoined)");

            DbCommand insertCmd = db.GetSqlStringCommand(sbSQL.ToString());
            db.AddInParameter(insertCmd, "FID", DbType.Int32, userOfActivityInfo.FID);
            db.AddInParameter(insertCmd, "FActivityID", DbType.Int32, userOfActivityInfo.FActivityID);
            db.AddInParameter(insertCmd, "FUserID", DbType.Int32, userOfActivityInfo.FUserID);
            db.AddInParameter(insertCmd, "FMoney", DbType.Decimal, userOfActivityInfo.FMoney);
            db.AddInParameter(insertCmd, "FMemo", DbType.String, userOfActivityInfo.FMemo);
            db.AddInParameter(insertCmd, "FJoined", DbType.Int32, userOfActivityInfo.FJoined);
            db.ExecuteNonQuery(insertCmd);
        }

        /// <summary>
        /// 更新用户与活动的对应关系
        /// </summary>
        /// <param name="userOfActivityInfo">用户与活动的对应关系信息类</param>
        public void UpdateUsersOfActivity(TUsersOfActivityInfo userOfActivityInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("Update TUsersOfActivity Set FActivityID=@FActivityID, FUserID=@FUserID, ");
            sbSQL.Append(" FMoney=@FMoney, FMemo=@FMemo, FJoined=@FJoined Where FID=@FID");

            DbCommand updateCmd = db.GetSqlStringCommand(sbSQL.ToString());
            db.AddInParameter(updateCmd, "FID", DbType.Int32, userOfActivityInfo.FID);
            db.AddInParameter(updateCmd, "FActivityID", DbType.Int32, userOfActivityInfo.FActivityID);
            db.AddInParameter(updateCmd, "FUserID", DbType.Int32, userOfActivityInfo.FUserID);
            db.AddInParameter(updateCmd, "FMoney", DbType.Decimal, userOfActivityInfo.FMoney);
            db.AddInParameter(updateCmd, "FMemo", DbType.String, userOfActivityInfo.FMemo);
            db.AddInParameter(updateCmd, "FJoined", DbType.Int32, userOfActivityInfo.FJoined);
            db.ExecuteNonQuery(updateCmd);
        }

        /// <summary>
        /// 删除用户与活动的对应关系
        /// </summary>
        /// <param name="id">关系ID</param>
        public void DeleteUsersOfActivity(int id)
        {
            string sql = "Delete from TUsersOfActivity Where FID=@FID";
            DbCommand deleteCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(deleteCmd, "FID", DbType.Int32, id);
            db.ExecuteNonQuery(deleteCmd);
        }

        /// <summary>
        /// 根据用户ID获取用户与活动的对应关系
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>用户与活动的对应关系信息类列表</returns>
        public DataSet GetUsersOfActivityByUserID(int userID)
        {
            string sql = "Select * from TUsersOfActivity Where FUserID=@FUserID";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(selectCmd, "FUserID", DbType.Int32, userID);
            DataSet dsUserAct = db.ExecuteDataSet(selectCmd);
            return dsUserAct;
        }

        /// <summary>
        /// 根据活动ID获取用户与活动的对应关系
        /// </summary>
        /// <param name="activityID">活动ID</param>
        /// <returns>用户与活动的对应关系信息类列表</returns>
        public DataSet GetUsersOfActivityByActivityID(int activityID)
        {
            string sql = "Select * from TUsersOfActivity Where FActivityID=@FActivityID";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(selectCmd, "FActivityID", DbType.Int32, activityID);
            DataSet dsUserAct = db.ExecuteDataSet(selectCmd);
            return dsUserAct;
        }

        /// <summary>
        /// 根据用户ID和活动ID获取用户与活动的对应关系
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="activityID">活动ID</param>
        /// <returns>用户与活动的对应关系信息类</returns>
        public TUsersOfActivityInfo GetUsersOfActivity(int userID, int activityID)
        {
            string sql = "Select * from TUsersOfActivity Where FUserID=@FUserID and FPassword=@FPassword";
            TUsersOfActivityInfo userActInfo = new TUsersOfActivityInfo();
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(selectCmd, "FUserID", DbType.Int32, userID);
            db.AddInParameter(selectCmd, "FActivityID", DbType.Int32, activityID);
            DataSet dsUserAct = db.ExecuteDataSet(selectCmd);
            foreach (DataRow dr in dsUserAct.Tables[0].Rows)
            {
                userActInfo.FID = Convert.ToInt32(dr["FID"]);
                userActInfo.FActivityID = Convert.ToInt32(dr["FActivityID"]);
                userActInfo.FUserID = Convert.ToInt32(dr["FUserID"]);
                userActInfo.FMemo = dr["FMemo"].ToString();
                if (dr["FMoney"] != null && dr["FMoney"] != DBNull.Value)
                {
                    userActInfo.FMoney = Convert.ToDecimal(dr["FMoney"]);
                }
                if (dr["FJoined"] != null && dr["FJoined"] != DBNull.Value)
                {
                    int joined = Convert.ToInt32(dr["FJoined"]);
                    if (joined == 1)
                        userActInfo.FJoined = true;
                    else
                        userActInfo.FJoined = false;
                }
            }
            return userActInfo;
        }

        #endregion
    }
}
